package com.king.student.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

import com.king.student.entity.Course;
import com.king.student.entity.StudentCourse;
import com.king.student.sql.SqlUtility;

public class StudentCourseDao {

	//添加一条学生课程的关系
	public static boolean addStudentCourse(StudentCourse studentCourse){
		try {
			String sql = "insert into student_course "
					+ "values(?,?,?)";
			PreparedStatement ps = SqlUtility.getConnection().prepareStatement(sql);
			ps.setString(1, studentCourse.getStudentID());
			ps.setString(2, studentCourse.getCourseID());
			ps.setInt(3, studentCourse.getGrade());
			ps.execute();
			return true;
		} catch (SQLException e) {
		}
		return false;
	}
	
	//删除一条学生成绩
	public static boolean delStudentCourse(String studentID, String courseID){
		try {
			String sql = "update student_course set grade=? where studentID=? and courseID=?";
			PreparedStatement ps = SqlUtility.getConnection().prepareStatement(sql);
			ps.setInt(1, 0);
			ps.setString(2, studentID);
			ps.setString(3, courseID);
			ps.execute();
			return true;
		} catch (SQLException e) {
		}
		return false;
	}
	
	//更新一条学生成绩
	public static boolean updateStudentCourse(StudentCourse studentCourse){
		try {
			String sql = "update student_course set grade=? where studentID=? and courseID=?";
			PreparedStatement ps = SqlUtility.getConnection().prepareStatement(sql);
			ps.setInt(1, studentCourse.getGrade());
			ps.setString(2, studentCourse.getStudentID());
			ps.setString(3, studentCourse.getCourseID());
			ps.execute();
			return true;
		} catch (SQLException e) {
		}
		return false;
	}
	
	//根据学号查询学生课程的关系
	public static List<StudentCourse> getStudentCourseByStudentID(String studentID){
		try {
			String sql = "select * from student_course where studentID=?";
			PreparedStatement ps = SqlUtility.getConnection().prepareStatement(sql);
			ps.setString(1, studentID);
			ResultSet rs = ps.executeQuery();
			
			List<StudentCourse> list = new ArrayList<>();
			while(rs.next()){
				StudentCourse studentCourse = new StudentCourse();
				studentCourse.setStudentID(rs.getString("studentID"));
				studentCourse.setCourseID(rs.getString("courseID"));
				studentCourse.setGrade(rs.getInt("grade"));
				list.add(studentCourse);
			}
			return list;
		} catch (SQLException e) {
		}
		return Collections.emptyList();
	}
	
	//查询未选修的课程
	public static List<Course> getStudentCourseByStudentNot(String studentID){
		try {
			String sql = "select * from course"
					+" where courseID not in"
					+" (select courseID from student_course"
					+" where studentID=?)";
			PreparedStatement ps = SqlUtility.getConnection().prepareStatement(sql);
			ps.setString(1, studentID);
			ResultSet rs = ps.executeQuery();
			List<Course> list = new ArrayList<>();
			while(rs.next()){
				Course course = new Course();
				course.setCourseID(rs.getString("courseID"));
				course.setCourseName(rs.getString("courseName"));
				course.setCourseTeacher(rs.getString("courseTeacher"));
				list.add(course);
			}
			return list;
		} catch (SQLException e) {
		}
		return Collections.emptyList();
	}
	
}
